package com.seadragon.app.ksr.dao.jpa;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.springframework.stereotype.Repository;

import com.seadragon.app.ksr.dao.ZipLookUpDao;
import com.seadragon.app.ksr.model.tiger.ZipLookUp;

@Repository("zipLookupDao")
public class ZipLookUpDaoJpaImpl extends AbstractJpaDao implements ZipLookUpDao {

	@Override
	public ZipLookUp findByZip5(String zip5) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<ZipLookUp> cq = builder.createQuery(ZipLookUp.class);
		Root<ZipLookUp> root = cq.from(ZipLookUp.class);
		cq.select(root);
		cq.where(builder.equal(root.get("zip"), zip5));

		return entityManager.createQuery(cq).getSingleResult();
	}

	@Override
	public List<String> findCitiesByCounty(String county) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<String> query = builder.createQuery( String.class );
		Root<ZipLookUp> root = query.from( ZipLookUp.class );
		query.select(root.get("city").<String>get("city")).distinct(true);
		query.where( builder.equal( root.get("county"), county));
		return entityManager.createQuery(query).getResultList();
	}

	@Override
	public List<String> findSiblingsByCity(String city, String county) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<String> query = builder.createQuery( String.class );
		Root<ZipLookUp> root = query.from( ZipLookUp.class );
		query.select(root.get("city").<String>get("city")).distinct(true);
		query.where( builder.and(builder.equal( root.get("county"), county), builder.notEqual(root.get("city"), city)));
		return entityManager.createQuery(query).getResultList();
	}

	@Override
	public List<ZipLookUp> findByCity(String city) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<ZipLookUp> cq = builder.createQuery(ZipLookUp.class);
		Root<ZipLookUp> root = cq.from(ZipLookUp.class);
		cq.select(root);
		cq.where(builder.equal(root.get("city"), city));

		return entityManager.createQuery(cq).getResultList();
	}

	@Override
	public String findCountyByZip(String zip) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<String> query = builder.createQuery( String.class );
		Root<ZipLookUp> root = query.from( ZipLookUp.class );
		query.where( builder.equal( root.get("zip"), zip));
		return entityManager.createQuery(query).getSingleResult();
	}

	@Override
	public String findCountyByCity(String city, String state) {
		CriteriaBuilder builder = entityManager.getCriteriaBuilder();
		CriteriaQuery<String> query = builder.createQuery( String.class );
		Root<ZipLookUp> root = query.from( ZipLookUp.class );
		query.select(root.get("county").<String>get("county")).distinct(true);
		query.where( builder.and(builder.equal( root.get("city"), city), builder.equal(root.get("state"), state)));
		return entityManager.createQuery(query).getSingleResult();
	}

}
